package com.sduept.nwld.kpi.manager;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sduept.core.dao.SuperDAO;
import com.sduept.nwld.dataserver.enums.FieldType;
import com.sduept.nwld.kpi.datasource.KpiDataElement;
import com.sduept.nwld.kpi.datasource.KpiDataObject;
import com.sduept.nwld.kpi.datasource.KpiDataSource;
import com.sduept.nwld.kpi.datasource.KpiQueryCondition;
import com.sduept.utils.DateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.text.ParseException;
import java.util.List;
import java.util.TreeSet;

@Service
public class KpiPersistentService {
	@Resource
	private DataSource server_datasource;

	@Autowired
	private SuperDAO dao;
	
	//@Autowired
	//private ExceptionHandlerService edao;
	
	
	
	public int count(KpiDataSource kds,List<KpiQueryCondition> conditions) throws SQLException{
		int rtn = 0;
		Connection conn = null;
		Statement st =  null;
		StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM "+kds.getTableName()+ " WHERE ");
		genQueryCondition(sql,conditions);
		try {
			conn = server_datasource.getConnection();
			st = conn.createStatement();
			ResultSet rs =  st.executeQuery(sql.toString());
			while(rs.next()){
				rtn = rs.getInt(1);;
			}
		} finally{
			st.close();
			conn.close();
		}
		return rtn;
	}
	public JSONArray  query(KpiDataSource kds,List<KpiQueryCondition> conditions) throws SQLException{
		JSONArray array = new JSONArray();
		Connection conn = null;
		Statement st =  null;
		StringBuilder sql = new StringBuilder();
		genQueryStatementStr(sql,kds);
		sql.append(" WHERE ");
		genQueryCondition(sql,conditions);
		try {
			conn = server_datasource.getConnection();
			st = conn.createStatement();
			ResultSet rs =  st.executeQuery(sql.toString());
			while(rs.next()){
				 JSONObject jsonObj = new JSONObject();  
			        // 遍历每一列  
				 	int i = 1;
				 	for(KpiDataElement d : kds.getProperties()){
			            String columnName =d.getField();  
			            Object value = rs.getObject(i);  
			            jsonObj.put(columnName, value);  
			            i++;
			        }   
			        array.add(jsonObj);   
			}
		} finally{
			st.close();
			conn.close();
		}
		return array;
	}
	private void genQueryCondition(StringBuilder sql,List<KpiQueryCondition> conditions){
		for (int i =0; i < conditions.size();i++) {
			if(i > 0){
				sql.append(" AND ");
			}
			sql.append(conditions.get(i).getContent());
		}
	}
	private void genQueryStatementStr(StringBuilder sb ,KpiDataSource kds
			) {
		//INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;
		//select f1,f2,f3... from table 
		sb.append("SELECT ");
		int len = kds.getProperties().size();
		int i = 0;
		for (KpiDataElement d : kds.getProperties()) {
			sb.append("`"+d.getField()+"`");
			if (i != len - 1) {
				sb.append(",");
			}
			i++;
		}
		sb.append(" FROM ");
		sb.append(kds.getTableName());
	}
	
	private void delete(Connection conn, String tableName) throws SQLException{
		Statement st = conn.createStatement();
		st.executeUpdate("DELETE FROM `" + tableName+"`");
		st.close();
	}
	private void insert(Connection conn,KpiDataSource kd,List<KpiDataObject> datas) throws SQLException, IOException{
		String sql = genPreparedStatementStr(kd.getTableName(),
				kd.getProperties());
		PreparedStatement pst = conn.prepareStatement(sql);
		try {
			int i = 0;
			for (KpiDataObject pd : datas) {
				for (KpiDataElement d : pd.datas) {
					try{
						putPrepareStatement(d.getValue(),d.getFieldOrder(),d.getType(), pst);
//						if (FieldType.File.name().equals(
//								d.getType()) || FieldType.Blob.name().equals(
//										d.getType())) {
//							String fname = ObjectId.get().toString();
//							String fpath = kd.filePath+File.separator+kd.tableName;
//							File f = new File(fpath);
//							if(!f.exists()){
//								f.mkdirs();
//							}
//							fname = fpath+File.separator+fname;
//							pst.setString(d.getFieldOrder(), fname);
//							Base64.decodeToFile(d.getValue(), fname);
//						}
					} catch (Exception e) {
						pst.setObject(d.getFieldOrder(), null);
						try {
							//TODO 记录异常信息
							//edao.logException(null, e);
						} catch (Exception e1) {
							e1.printStackTrace();
						}
					}
				}
				pst.addBatch();
				i++;
				if (i == 1000) {
					pst.executeBatch();
					pst.clearBatch();
					i = 0;
				}
			}
			pst.executeBatch();
		} finally {
			pst.close();
		}
	
	}
	private void update(Connection conn,KpiDataSource ri,List<KpiDataObject> datas) throws SQLException, IOException{
		String sql = genUpdateStatementStr(ri.getTableName(),
				ri.properties);
		PreparedStatement pst = conn.prepareStatement(sql);
		try {
			int i = 0;
			for (KpiDataObject pd : datas) {
				KpiDataElement idkey = null;
				for (KpiDataElement d : pd.datas) {
					if(d.isIdKey()){
						idkey = d;
					}
					try{
						putPrepareStatement(d.getValue(),d.getFieldOrder(),d.getType(), pst);
//						if (FieldType.File.name().equals(
//								d.getType()) || FieldType.Blob.name().equals(
//										d.getType())) {
//							String fname = ObjectId.get().toString();
//							String fpath = ri.filePath+File.separator+ri.tableName;
//							File f = new File(fpath);
//							if(!f.exists()){
//								f.mkdirs();
//							}
//							fname = fpath+File.separator+fname;
//							pst.setString(d.getFieldOrder(), fname);
//							Base64.decodeToFile(d.getValue(), fname);
//						}
					} catch (Exception e) {
						pst.setObject(d.getFieldOrder(), null);
						try {
//							edao.logException(ri, e);
						} catch (Exception e1) {
							e1.printStackTrace();
						}
					}
					
				}
				if(idkey != null){
					try {
						putPrepareStatement(idkey.getValue(),pd.datas.size()+1,idkey.getType(),pst);
					} catch (Exception e) {
					}
				}
				pst.addBatch();
				i++;
				if (i == 1000) {
					pst.executeBatch();
					pst.clearBatch();
					i = 0;
				}
			}
			pst.executeBatch();
		} finally {
			pst.close();
		}
		
	}
	private void updateToHistory(Connection conn,KpiDataSource ri,List<KpiDataObject> datas) throws SQLException, IOException{
		String sql = genUpdateHistoryStatementStr(ri.getTableName(),ri.properties);
		String updatesql = "UPDATE `"+ri.getTableName()+KpiDataDbManager.HISTORY_TABLE_SUFFIX+"` SET `"
				+KpiDataDbManager.HISTORY_TABLE_UPDATE_COLUMN+"`='"+new Timestamp(System.currentTimeMillis())+"' WHERE `"
				+KpiDataDbManager.HISTORY_TABLE_UPDATE_COLUMN+"` IS NULL";
		Statement pst = conn.createStatement();
		Statement pst2 = conn.createStatement();
		try {
			pst.executeUpdate(sql);
			pst2.executeUpdate(updatesql);
		} finally {
			pst.close();
			pst2.close();
		}
		
	}

	private void putPrepareStatement(String value,int parameterIndex,String fieldType,PreparedStatement pst) throws Exception{
		if (value != null && value.trim().length() > 0) {
			if (FieldType.String.name().equals(fieldType)) {
				pst.setString(parameterIndex,
						value);
			} else if (FieldType.Integer.name().equals(
					fieldType)) {
				pst.setInt(parameterIndex,
						Integer.valueOf(value));
			} else if (FieldType.Float.name().equals(
					fieldType)) {
				pst.setFloat(parameterIndex,
						Float.valueOf(value));
			} else if (FieldType.Double.name().equals(
					fieldType)) {
				pst.setDouble(parameterIndex,
						Double.valueOf(value));
			} else if (FieldType.Long.name().equals(
					fieldType)) {
				pst.setLong(parameterIndex,
						Long.valueOf(value));
			} else if (FieldType.DateTime.name().equals(
					fieldType)) {
				pst.setTimestamp(parameterIndex,
							Timestamp.valueOf(value));
			} else if (FieldType.Date.name().equals(
					fieldType)) {
				pst.setDate(parameterIndex,
							new java.sql.Date(DateUtil.getDateByString(value, DateUtil.pattern_ymd).getTime()));
			} 
			
		} else {
			pst.setObject(parameterIndex, null);
		}
	}
	public void persistent(KpiDataSource ri, List<KpiDataObject> datas)
			throws SQLException, IOException, ParseException {
		if (datas.size() > 0) {
			Connection conn = null;
			try {
				conn = server_datasource.getConnection();
				//删除后插入
				if (ri.getPersistenceType() == 2) {
					delete(conn,ri.getTableName());
					insert(conn,ri,datas);
				} else if (ri.getPersistenceType() == 0) {
					insert(conn,ri,datas);
				} else if (ri.getPersistenceType() == 1) {
					//更新必须指定一个key
					update(conn,ri,datas);
				} else if (ri.getPersistenceType() == 3) {
					//更新到历史表,然后删除后插入
					updateToHistory(conn,ri,datas);
					delete(conn,ri.getTableName());
					insert(conn,ri,datas);
				}

			} finally {
				conn.close();
			}
		}
	}

	private String genPreparedStatementStr(String tableName,
			TreeSet<KpiDataElement> datas) {
		// String sql =
		// "INSERT INTO t_cim_model_map(pathName,name,sname,measuretype) VALUES(?,?,?,?)";
		int len = datas.size();


		StringBuilder sb = new StringBuilder("INSERT INTO `" + tableName + "`(");
		int i = 0;
		for (KpiDataElement d : datas) {
			sb.append("`"+d.getField()+"`");
			if (i != len - 1) {
				sb.append(",");
			}
			i++;
		}
		sb.append(") VALUES(");
		for (i = 0; i < len; i++) {
			sb.append("?");
			if (i != len - 1) {
				sb.append(",");
			}
		}
		sb.append(")");
		return sb.toString();
	}
	
	private String genUpdateStatementStr(String tableName,
			TreeSet<KpiDataElement> datas) {
//		String sql = "update t_customer set LICENSE_KEY=?,CORPORATE_NAME=?,INTEGRATED_CLASSIFICATION=?,BOSSHEAD=?," +  
//	            "CONTACT_PHONE=?,ORDER_FREQUENCY=?,CONTACT_ADDRESS=?,USER_ID=? where CUSTOMER_ID=?";
		int len = datas.size();
		
		StringBuilder sb = new StringBuilder("UPDATE `" + tableName + "` SET ");
		int i = 0;
		String where = null;
		for (KpiDataElement d : datas) {
			if(d.isIdKey()){
				where = " WHERE `"+d.getField()+"`=?";
			}
			sb.append("`"+d.getField()+"`=?");
			if (i != len - 1) {
				sb.append(",");
			}
			i++;
		}
		sb.append(where);
		return sb.toString();
	}
	private String genUpdateHistoryStatementStr(String tableName,TreeSet<KpiDataElement> datas
			) {
		//INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;
		
		StringBuilder sb = new StringBuilder("INSERT INTO `" + tableName+KpiDataDbManager.HISTORY_TABLE_SUFFIX+"` (");
		int len = datas.size();

		int i = 0;
		for (KpiDataElement d : datas) {
			sb.append("`"+d.getField()+"`");
			if (i != len - 1) {
				sb.append(",");
			}
			i++;
		}
		sb.append(") SELECT ");
		i=0;
		for (KpiDataElement d : datas) {
			sb.append("`"+d.getField()+"`");
			if (i != len - 1) {
				sb.append(",");
			}
			i++;
		}
		sb.append(" FROM `"+tableName+"`");
		return sb.toString();
	}
	public static void main(String[] arges){
//		String f = "d:\\a1\\a11\\a111";
//		File fi = new File(f);
//		fi.mkdirs();
//		List<KpiDataElement> eles = new ArrayList<KpiDataElement>();
//		KpiDataElement e = new KpiDataElement();
//		 e = new KpiDataElement();
//			e.setField("mProtectName");
//			e.setSoapObjectProperty("cim:mProtectName");
//			e.setFieldOrder(1);
//			eles.add(e);
//			 e = new KpiDataElement();
//				e.setField("mRID");
//				e.setSoapObjectProperty("cim:mRID");
//				e.setIdKey(true);
//				e.setFieldOrder(2);
//				eles.add(e);
//			 e = new KpiDataElement();
//				e.setField("sid");
//				e.setSoapObjectProperty("cim:mStationID");
//				e.setFieldOrder(3);
//				eles.add(e);
//				e = new KpiDataElement();
//				e.setField("mEventStartTime");
//				e.setFieldOrder(4);
//				e.setSoapObjectProperty("cim:mEventStartTime");
//				eles.add(e);
//				e = new KpiDataElement();
//				e.setField("mReceviceTime");
//				e.setFieldOrder(5);
//				e.setSoapObjectProperty("cim:mReceviceTime");
//				eles.add(e);
//		log.info(new PersistentService().genUpdateHistoryStatementStr("t_test", eles ));
	}
}
